﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WinFormsAppTest.Class;
using Microsoft.VisualBasic.ApplicationServices;
using WinFormsAppTest.pojo;

namespace WinFormsAppTest.Dao
{
    internal class AdminDao
    {
        //使用hyl版本进行登录
        //String connectionString = @"Data Source=DESKTOP-SQERB5L\MSSQLSERVERR;" +
        //    "Initial Catalog=hylpersonsystem;" +
        //    "User ID=hyl;" +
        //    "Password=123456;";

        //使用window登录
        String connectionString = @"Data Source=DESKTOP-SQERB5L\MSSQLSERVERR;" +
            "Initial Catalog=hylpersonsystem;" +
            "Integrated Security=true;";

        //public SqlConnection connect(String aname ,String apassword)
        //{

        //    using (SqlConnection sc = new SqlConnection(connectionString))
        //    {  ///
        //        try
        //        {
        //            sc.Open();

        //            // 检查连接状态
        //            if (sc.State == ConnectionState.Open)
        //            {
        //                Console.WriteLine("数据库连接成功！");
        //                MessageBox.Show("数据库连接成功！");
        //                FindIsUser(aname, apassword);
        //                //sc.Close();

        //                // 连接成功后可以执行其他操作
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            Console.WriteLine("数据库连接失败：" + ex.Message);
        //            MessageBox.Show("数据库连接失败！");
        //            sc.Close();  
        //        }
        //        return sc;
        //    }///
        //}


        //connect 无参
        public SqlConnection connect()
        {

            using (SqlConnection sc = new SqlConnection(connectionString))
            {  ///
                sc.Open();
                try
                {
                    //sc.Open();

                    // 检查连接状态
                    if (sc.State == ConnectionState.Open)
                    {
                        Console.WriteLine("数据库连接成功！");
                        MessageBox.Show("数据库连接成功！");
                        sc.Close();

                        // 连接成功后可以执行其他操作
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("数据库连接失败：" + ex.Message);
                    MessageBox.Show("数据库连接失败！");
                    sc.Close();
                }
                return sc;
            }///
        }
        //


        //判断是否在user表内,登录界面逻辑
        //如果登录成功，则返回用户id
        public int FindIsAdmin(String uname, String upassword)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            using (SqlCommand command = new SqlCommand("SELECT * FROM admin", sc))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int id = reader.GetInt32(0);
                        String adminname = reader.GetString(1);
                        String password = reader.GetString(2);
                        if (adminname == uname && password == upassword)
                        {
                            // MessageBox.Show("登录成功！");
                            sc.Close();

                            return id;
                        }
                    }
                    //MessageBox.Show("登录失败！");
                    //MessageBox.Show(uname+" "+ upassword);

                    sc.Close();
                    return 0;
                }
            }
        }


        //删除用户信息
        public void DeleteUserById(int id)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "delete from users where userid=@UserId";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("注册时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@UserId", id);
                command.ExecuteNonQuery();
                sc.Close();
                return;
            }
        }


        //更新用户信息
        public void UpdateUserById(int id,string uname,string upassword)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "update users set name=@UserName,password=@UserPassword where userid=@UserId";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("注册时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@UserId", id);
                command.Parameters.AddWithValue("@UserName", uname);
                command.Parameters.AddWithValue("@UserPassword", upassword);
                command.ExecuteNonQuery();
                sc.Close();
                return;
            }
        }



        //根据id获取用户全部信息
        public List<WinFormsAppTest.pojo.User> GetUserById(int id)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from users where userid = @Userid";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<WinFormsAppTest.pojo.User> users = new List<WinFormsAppTest.pojo.User>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int userid = reader.GetInt32(0);
                        String name = reader.GetString(1);
                        String password = reader.GetString(2);
                        WinFormsAppTest.pojo.User f = new WinFormsAppTest.pojo.User(userid, name, password);
                        users.Add(f);
                    }
                    sc.Close();
                    return users;
                }
            }
        }
        //

        //根据用户名获取用户全部信息
        public List<WinFormsAppTest.pojo.User> GetUserByName(string uname)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from users where name = @UserName";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@UserName", uname);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<WinFormsAppTest.pojo.User> users = new List<WinFormsAppTest.pojo.User>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int userid = reader.GetInt32(0);
                        String name = reader.GetString(1);
                        String password = reader.GetString(2);
                        WinFormsAppTest.pojo.User f = new WinFormsAppTest.pojo.User(userid, name, password);
                        users.Add(f);
                    }
                    sc.Close();
                    return users;
                }
            }
        }
        //


        //根据密码获取用户全部信息
        public List<WinFormsAppTest.pojo.User> GetUserByPassword(string upassword)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from users where password = @Password";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Password", upassword);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<WinFormsAppTest.pojo.User> users = new List<WinFormsAppTest.pojo.User>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int userid = reader.GetInt32(0);
                        String name = reader.GetString(1);
                        String password = reader.GetString(2);
                        WinFormsAppTest.pojo.User f = new WinFormsAppTest.pojo.User(userid, name, password);
                        users.Add(f);
                    }
                    sc.Close();
                    return users;
                }
            }
        }
        //


        //通过获取用户全部信息
        public List<WinFormsAppTest.pojo.User> GetUsers()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from users";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<WinFormsAppTest.pojo.User> users = new List<WinFormsAppTest.pojo.User>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int userid = reader.GetInt32(0);
                        String name = reader.GetString(1);
                        String password = reader.GetString(2);
                        WinFormsAppTest.pojo.User f = new WinFormsAppTest.pojo.User(userid, name, password);
                        users.Add(f);
                    }
                    sc.Close();
                    return users;
                }
            }
        }
        //


        //admin 主体代码

        //添加管理员信息
        public void CreateAdmin(String uname, String upassword)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "insert into admin values(@Username,@Password)";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("注册时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Username", uname);
                command.Parameters.AddWithValue("@Password", upassword);
                command.ExecuteNonQuery();
                //MessageBox.Show("注册成功！");
                sc.Close();
                return;
            }
        }



        //删除用户信息
        public void DeleteAdminById(int id)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "delete from admin where adminid=@AdminId";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("注册时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@AdminId", id);
                command.ExecuteNonQuery();
                sc.Close();
                return;
            }
        }


        //更新用户信息
        public void UpdateAdminById(int id, string uname, string upassword)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "update admin set name=@AdminName,password=@AdminPassword where adminid=@AdminId";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("注册时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@AdminId", id);
                command.Parameters.AddWithValue("@AdminName", uname);
                command.Parameters.AddWithValue("@AdminPassword", upassword);
                command.ExecuteNonQuery();
                sc.Close();
                return;
            }
        }



        //根据id获取用户全部信息
        public List<WinFormsAppTest.pojo.Admin> GetAdminById(int id)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from admin where adminid = @Adminid";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Adminid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<WinFormsAppTest.pojo.Admin> admins = new List<WinFormsAppTest.pojo.Admin>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int adminid = reader.GetInt32(0);
                        String name = reader.GetString(1);
                        String password = reader.GetString(2);
                        WinFormsAppTest.pojo.Admin f = new WinFormsAppTest.pojo.Admin(adminid, name, password);
                        admins.Add(f);
                    }
                    sc.Close();
                    return admins;
                }
            }
        }
        //

        //根据用户名获取用户全部信息
        public List<WinFormsAppTest.pojo.Admin> GetAdminByName(string uname)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from admin where name = @AdminName";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@AdminName", uname);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<WinFormsAppTest.pojo.Admin> users = new List<WinFormsAppTest.pojo.Admin>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int userid = reader.GetInt32(0);
                        String name = reader.GetString(1);
                        String password = reader.GetString(2);
                        WinFormsAppTest.pojo.Admin f = new WinFormsAppTest.pojo.Admin(userid, name, password);
                        users.Add(f);
                    }
                    sc.Close();
                    return users;
                }
            }
        }
        //

        //通过获取用户全部信息
        public List<WinFormsAppTest.pojo.Admin> GetAdmins()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from admin";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<WinFormsAppTest.pojo.Admin> users = new List<WinFormsAppTest.pojo.Admin>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int userid = reader.GetInt32(0);
                        String name = reader.GetString(1);
                        String password = reader.GetString(2);
                        WinFormsAppTest.pojo.Admin f = new WinFormsAppTest.pojo.Admin(userid, name, password);
                        users.Add(f);
                    }
                    sc.Close();
                    return users;
                }
            }
        }
        //

        //admin主体结束



        //以下的的dao层代码，只有各个表获取本表所有信息的数据访问，
        //其他的获取方式以放在各种表的dao层。

        //获取档案信息
        public List<Files> GetUserFiles()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from files ";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Files> files = new List<Files>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int filesid = reader.GetInt32(0);
                        String msg = reader.GetString(1);
                        String house = reader.GetString(2);
                        String contract = reader.GetString(3);
                        String insure = reader.GetString(4);
                        int userid = reader.GetInt32(5);
                        Files f = new Files(filesid, msg, house, contract,
                                            insure, userid);
                        files.Add(f);
                    }
                    sc.Close();
                    return files;
                }
            }
        }
        //


        //获取人事变动信息
        public List<Change> GetUserChange()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from change ";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Change> change = new List<Change>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int changeid = reader.GetInt32(0);
                        String type = reader.GetString(1);
                        DateTime time = reader.GetDateTime(2);
                        int userid = reader.GetInt32(3);
                        Change f = new Change(changeid, type, time, userid);
                        change.Add(f);
                    }
                    sc.Close();
                    return change;
                }
            }
        }
        //

        //获取考核奖惩信息
        public List<Exam> GetUserExam()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from exam ";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Exam> exam = new List<Exam>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int examid = reader.GetInt32(0);
                        String state = reader.GetString(1);
                        String measure = reader.GetString(2);
                        String goal = reader.GetString(3);
                        int userid = reader.GetInt32(4);
                        Exam f = new Exam(examid, state, measure, goal, userid);
                        exam.Add(f);
                    }
                    sc.Close();
                    return exam;
                }
            }
        }
        //


        //获取考勤编号信息
        public List<Work> GetUserWork()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from work ";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
               // command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Work> work = new List<Work>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int workid = reader.GetInt32(0);
                        int userid = reader.GetInt32(1);
                        Work f = new Work(workid, userid);
                        work.Add(f);
                    }
                    sc.Close();
                    return work;
                }
            }
        }
        //

        //获取考勤的签到信息
        public List<Signs> GetUserSigns()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from signs ";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
               // command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Signs> signs = new List<Signs>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int signid = reader.GetInt32(0);
                        DateTime begintime = reader.GetDateTime(1);
                        DateTime endtime = reader.GetDateTime(2);
                        int workid = reader.GetInt32(3);
                        int userid = reader.GetInt32(4);
                        Signs f = new Signs(signid, begintime, endtime, workid, userid);
                        signs.Add(f);
                    }
                    sc.Close();
                    return signs;
                }
            }
        }
        //

        //获取考勤的加班信息
        public List<Overtime> GetUserOvertime()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from overtime ";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Overtime> overtime = new List<Overtime>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int overid = reader.GetInt32(0);
                        DateTime begintime = reader.GetDateTime(1);
                        DateTime endtime = reader.GetDateTime(2);
                        int workid = reader.GetInt32(3);
                        int userid = reader.GetInt32(4);
                        Overtime f = new Overtime(overid, begintime, endtime, workid, userid);
                        overtime.Add(f);
                    }
                    sc.Close();
                    return overtime;
                }
            }
        }
        //


        //获取考勤的出差信息
        public List<Outtime> GetUserOuttime()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from outtime ";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Outtime> outtime = new List<Outtime>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int outid = reader.GetInt32(0);
                        DateTime begintime = reader.GetDateTime(1);
                        DateTime endtime = reader.GetDateTime(2);
                        String place = reader.GetString(3);
                        int workid = reader.GetInt32(4);
                        int userid = reader.GetInt32(5);
                        Outtime f = new Outtime(outid, begintime, endtime, place, workid, userid);
                        outtime.Add(f);
                    }
                    sc.Close();
                    return outtime;
                }
            }
        }
        //

    }
}
